"""
Case Type   : DBlink功能
Case Name   : 开启线程池，验证dblink功能
Create At   : 2024/7/1
Owner       : peilinqian
Description :
    step1: 修改参数;
    step2: 创建数据库及库下创建dblink扩展;
    step3: 目标库进行数据库及用户建立;
    step4: 目标库进行pg_hba用户白名单配置;
    step5: 进行dblink连接;
Expect      :
    step1: 修改参数; expect:修改成功
    step2: 创建数据库及库下创建dblink扩展; expect:成功
    step3: 目标库进行数据库及用户建立; expect:成功
    step4: 目标库进行pg_hba用户白名单配置; expect:成功
    step5: 进行dblink连接; expect:失败
History     :
"""
import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro

com = Common()


@unittest.skipIf(not com.check_node_exists('remote1_PrimaryDbuser'),
                 '无远程主机环境不执行')
class DblinkCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)}:初始化----')
        self.pri_dbuser = Node(node='PrimaryDbUser')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.remote_db = Node(node='remote1_PrimaryDbuser')
        self.remote_sh = CommonSH('remote1_PrimaryDbuser')
        self.com = Common()
        self.constant = Constant()
        self.db_name_1 = 'db_dblink_0024'
        self.u_sys = 'u_dblink_sys0024'
        self.err_flag = 'ERROR:  dblink not support in thread pool'
        self.log.info('----查询参数----')
        self.init_para = self.com.show_param("enable_thread_pool")

    def test_main(self):
        step_txt = '----step1: 修改参数; expect:修改成功----'
        self.log.info(step_txt)
        if self.init_para == 'off':
            msg = self.pri_sh.execute_gsguc('set',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            f"enable_thread_pool= on", )
            self.assertTrue(msg, '执行失败:' + step_txt)
            restart_result = self.pri_sh.restart_db_cluster()
            self.assertTrue(restart_result)
            self.log.info('----查询参数----')
            self.com.show_param("enable_thread_pool")
        else:
            self.log.info('线程池模式已打开')

        step_txt = '----step2: 创建数据库及库下创建dblink扩展; expect:成功----'
        self.log.info(step_txt)
        db_sql = f"drop database if exists {self.db_name_1};" \
                 f"create database {self.db_name_1};"
        db_result = self.pri_sh.execut_db_sql(db_sql)
        self.log.info(db_result)
        create_sql = f"create extension dblink;"
        create_result = self.pri_sh.execut_db_sql(create_sql,
                                                  dbname=self.db_name_1)
        self.log.info(create_result)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, db_result,
                      "执行失败" + step_txt)
        self.assertIn(self.constant.create_extension_success, create_result,
                      "执行失败" + step_txt)

        step_txt = '----step3: 目标库进行数据库及用户建立; expect:成功----'
        self.log.info(step_txt)
        db_sql = f"drop database if exists {self.db_name_1};" \
                 f"create database {self.db_name_1};"
        msg1 = self.remote_sh.execut_db_sql(db_sql,
                                            env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(msg1)
        user_sql = f"drop user if exists {self.u_sys} cascade;" \
                   f"create user {self.u_sys} sysadmin " \
                   f"password '{macro.PASSWD_INITIAL}';"
        msg2 = self.remote_sh.execut_db_sql(user_sql,
                                            env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(msg2)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, msg1,
                      "执行失败" + step_txt)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, msg2,
                      "执行失败" + step_txt)

        step_txt = '----step4: 目标库进行pg_hba用户白名单配置; expect:成功----'
        self.log.info(step_txt)
        self.log.info('----dblink端pg_hba配置----')
        guc_res = self.remote_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, macro.DB_INSTANCE_PATH_REMOTE1,
            f'host  {self.db_name_1}  all '
            f'{self.pri_dbuser.db_host}/32 sha256',
            macro.DB_ENV_PATH_REMOTE1)
        self.log.info(guc_res)
        self.assertTrue(guc_res, "执行失败" + step_txt)

        step_txt = '----step5: 进行dblink连接; expect:失败----'
        self.log.info(step_txt)
        sql = f"SELECT dblink_connect('dtest_1', " \
              f"'host={self.remote_db.db_host} " \
              f"port={self.remote_db.db_port} " \
              f"dbname={self.db_name_1} " \
              f"user={self.u_sys} " \
              f"password={macro.PASSWD_INITIAL}');" \
              f"select pg_sleep(10);"
        result = self.pri_sh.execut_db_sql(sql,
                                           dbname=self.db_name_1)
        self.log.info(result)
        self.assertIn(self.err_flag, result, "执行失败" + step_txt)

    def tearDown(self):
        self.log.info('----this is tearDown----')
        step6_txt = '----dblink侧清理数据; expect:清理成功----'
        self.log.info(step6_txt)
        drop_sql = f"drop database {self.db_name_1};" \
                   f"drop user if exists {self.u_sys} cascade;"
        msg1 = self.pri_sh.execut_db_sql(drop_sql)
        self.log.info(msg1)
        step7_txt = '----dblink还原参数 expect:还原成功----'
        self.log.info(step7_txt)
        if self.init_para == 'off':
            self.pri_sh.execute_gsguc('set',
                                      self.constant.GSGUC_SUCCESS_MSG,
                                      f"enable_thread_pool={self.init_para}")
            self.restart_result = self.pri_sh.restart_db_cluster()
            self.log.info('----查询参数----')
            self.com.show_param("enable_thread_pool")
        else:
            self.log.info('线程池模式初始值为on，无需变更')

        step8_txt = '----目标库进行数据库及用户删除; expect:成功----'
        self.log.info(step8_txt)
        drop_db_sql = f"clean connection to all force for database " \
                      f"{self.db_name_1};" \
                      f"drop database if exists {self.db_name_1};"
        drop_db_msg = self.remote_sh.execut_db_sql(
            drop_db_sql, env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(drop_db_msg)
        drop_user_sql = f"drop user if exists {self.u_sys} cascade;"
        drop_user_msg = self.remote_sh.execut_db_sql(
            drop_user_sql, env_path=macro.DB_ENV_PATH_REMOTE1)
        self.log.info(drop_user_msg)

        step9_txt = '----目标库进行pg_hba取消用户白名单配置; expect: 成功----'
        self.log.info(step9_txt)
        guc_res1 = self.remote_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG, '',
            'all', False, False, macro.DB_INSTANCE_PATH_REMOTE1,
            f'host  {self.db_name_1}  all '
            f'{self.pri_dbuser.db_host}/32',
            macro.DB_ENV_PATH_REMOTE1)
        self.log.info(guc_res1)

        self.log.info('----teardown断言----')
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, msg1,
                      "执行失败" + step6_txt)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, msg1,
                      "执行失败" + step6_txt)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, drop_db_msg,
                      "执行失败" + step8_txt)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, drop_user_msg,
                      "执行失败" + step8_txt)
        self.assertTrue(guc_res1, "执行失败" + step9_txt)
        if self.init_para == 'off':
            self.assertTrue(self.restart_result, "执行失败" + step7_txt)

        self.log.info(f'----{os.path.basename(__file__)}:执行完毕----')
